﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Architecture.DataLayer.DbAccess;
using CommonComponents.Configuration;
using CommonComponents.ExceptionHandling;
using LASLogic.BusinessLayer.DataModel;
using LASLogic.BusinessLayer.Utilities;

namespace LASLogic.DataLayer.Implementor
{
    /// <summary>
    /// Clase que maneja los accesos a la base de datos de las operaciones relativas a la empresa
    /// </summary>
    public class CompanyDataManager
    {
        /// <summary>
        /// Metodo que permite insertar una nueva compañía a la base de datos
        /// </summary>
        /// <param name="name">Nombre de la compañia</param>
        /// <param name="taxIdentification">Cedula Juridica de la compañia</param>
        /// <param name="logo">Logotipo de la compañía</param>
        /// <param name="systemCurrency">Moneda del sistema</param>
        /// <param name="localCurrency">Moneda Local</param>
        /// <returns>Indicador si la compañía se inserto en la base de datos</returns>
        public bool InsertNewCompany(string name, string taxIdentification, byte[] logo, string systemCurrency, string localCurrency)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_INSERT_COMPANY);
                var parameters = new SqlParameter[5];
                parameters[0] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_INSERT_COMPANY_COMPANY_NAME),
                                                 name);
                parameters[1] =
                    new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_INSERT_COMPANY_TAX_IDENTIFICATION),
                                     taxIdentification);
                parameters[2] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_INSERT_COMPANY_LOGO), logo);
                parameters[3] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_INSERT_COMPANY_LOCAL_CURRENCY), localCurrency);
                parameters[4] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_INSERT_COMPANY_SYSTEM_CURRENCY), systemCurrency);

                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                return GetResult(dataHelper.ExecuteStoredProcedure(spName, parameters), dataHelper);
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return false;
            }
        }

        /// <summary>
        /// Permite obtener todas las compañias de la base de datos
        /// </summary>
        /// <returns>Lista de compañias de la base de datos</returns>
        public List<Company> GetCompanies()
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_GET_COMPANIES);
                var parameters = new SqlParameter[0];
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                var companies = new List<Company>();
                while (result.Read())
                {
                    var companyId = (int)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANIES_COMPANY_ID)];
                    var companyName =
                        (string)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANIES_COMPANY_NAME)];
                    var taxIdentification =
                        (string)
                        result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANIES_COMPANY_TAX_IDENTIFICATION)];
                    //var logo = Image.FromStream(new MemoryStream((byte[])result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANIES_COMPANY_LOGO)]));
                    //var logo = (byte[])result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANIES_COMPANY_LOGO)];
                    byte[] logo = null;

                    companies.Add(new Company(companyId, companyName, taxIdentification, logo, null,null));
                }
                dataHelper.CloseReader();
                return companies;
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return null;
            }
        }

        /// <summary>
        /// Metodo que permite obtener una compañia a partir de un identificador de compañia
        /// </summary>
        /// <returns>Compañia que se deseaba obtener</returns>
        public Company GetCompany(int companyId)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_GET_COMPANY);
                var parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_GET_COMPANY_COMPANY_ID),
                                                 companyId);
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                Company company = null;
                while (result.Read())
                {
                    var companyName =
                        (string)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANIES_COMPANY_NAME)];
                    var taxIdentification =
                        (string)
                        result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANIES_COMPANY_TAX_IDENTIFICATION)];
                    //var logo = Image.FromStream(new MemoryStream((byte[])result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANIES_COMPANY_LOGO)]));
                    //var logo = (byte[])result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANIES_COMPANY_LOGO)];
                    company = new Company(companyId, companyName, taxIdentification, null, null, null);
                }
                dataHelper.CloseReader();
                return company;
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return null;
            }
        }


        /// <summary>
        /// Método que permite obtener todos los periodos contables de una compañia
        /// </summary>
        /// <param name="companyId">Identificador de la compañía</param>
        /// <returns>Periodos contables de la compañía</returns>
        public List<AccountingPeriod> GetAccountingPeriods(int companyId)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_GET_ACCOUNTING_PERIODS);
                var parameters = new SqlParameter[1];
                parameters[0] =
                    new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_GET_ACCOUNTING_PERIODS_COMPANY_ID),
                                     companyId);
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                var accountingPeriods = new List<AccountingPeriod>();
                while (result.Read())
                {
                    var periodId =
                        (int)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_ACCOUNTING_PERIODS_PERIOD_ID)];
                    var periodType =
                        (string)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_ACCOUNTING_PERIODS_PERIOD_TYPE)];
                    var description =
                        (string)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_ACCOUNTING_PERIODS_DESCRIPTION)];
                    var localBalance =
                        (decimal)
                        result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_ACCOUNTING_PERIODS_LOCAL_BALANCE)];
                    var systemBalance =
                        (decimal)
                        result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_ACCOUNTING_PERIODS_SYSTEM_BALANCE)];
                    accountingPeriods.Add(new AccountingPeriod(periodId, periodType, description, systemBalance,
                                                               localBalance));
                }
                dataHelper.CloseReader();
                return accountingPeriods;
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return null;
            }
        }


        /// <summary>
        /// Método que permite obtener todos los periodos contables de una compañia
        /// </summary>
        /// <param name="periodId">Indentificador del periodo</param>
        /// <returns>Compañia que se deseaba obtener</returns>
        public List<AccountingMonth> GetAccountingMonths(int periodId)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_GET_ACCOUNTING_MONTHS);
                var parameters = new SqlParameter[1];
                parameters[0] =
                    new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_GET_ACCOUNTING_MONTHS_PERIOD_ID), periodId);
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                var accountingMonths = new List<AccountingMonth>();
                while (result.Read())
                {
                    var monthId =
                        (int)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_ACCOUNTING_MONTHS_MONTH_ID)];
                    var status =
                        (string)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_ACCOUNTING_MONTHS_MONTH_STATUS)];
                    var startDate =
                        Convert.ToDateTime(
                        result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_ACCOUNTING_MONTHS_MONTH_START_DATE)].ToString());
                    var endDate =
                        Convert.ToDateTime(result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_ACCOUNTING_MONTHS_MONTH_END_DATE)].ToString());
                    var goodThru =
                        Convert.ToDateTime(
                        result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_ACCOUNTING_MONTHS_GOODTHRU_DATE)].ToString());
                    var validSince =
                       Convert.ToDateTime(
                        result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_ACCOUNTING_MONTHS_VALIDSINCE_DATE)].ToString());
                    var localBalance =
                        (decimal)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_ACCOUNTING_MONTHS_LOCAL_BALANCE)];
                    var systemBalance =
                        (decimal)
                        result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_ACCOUNTING_MONTHS_SYSTEM_BALANCE)];
                    accountingMonths.Add(new AccountingMonth(monthId, status, startDate, endDate, validSince, goodThru,
                                                             localBalance, systemBalance));
                }
                dataHelper.CloseReader();
                return accountingMonths;
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return null;
            }
        }


        /// <summary>
        /// Método que permite obtener el balance de comprobacion, el estado de resultados, o el balance general de una empresa
        /// </summary>
        /// <param name="companyId">Identificador unico de la compañía</param>
        /// <param name="reporte">Especifica el reporte que se quiere sacar de la base de dtos</param>
        /// <returns>Compañia que se desea obtener el balance</returns>
        public List<Reportes> GetReporte(int companyId, string reporte)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(reporte);
                var parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_GET_ALL_ENTRIES_COMPANY_ID), companyId);
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                var balanceComprobacion = new List<Reportes>();
                while (result.Read())
                {
                    balanceComprobacion.Add(new Reportes
                    {
                        Codigo =
                            (string)
                            result[
                                appConfig.GetProperty(
                                    Constants.SP_OUTPUT_GET_ACCOUNTING_CATALOG_ACCOUNT_CODE)],
                        NombreExtranjero =
                            (string)
                            result[
                                appConfig.GetProperty(
                                    Constants.SP_OUTPUT_GET_ACCOUNTING_CATALOG_ACCOUNT_FOREIGN_NAME)],
                        NombreCuenta =
                            (string)
                            result[
                                appConfig.GetProperty(Constants.SP_PARAM_INSERT_ACCOUNT_NAME)
                                ],
                        CedulaJuridica =
                                 (string)
                            result[
                                appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANIES_COMPANY_TAX_IDENTIFICATION)
                                ],
                        NombreEmpresa =
                         (string)
                            result[
                                appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANIES_COMPANY_NAME)
                                ],
                        //Todo hacer esto bonito
                        Tipo = (string)result["Tipo"],
                        TipoMoneda = (string)result["TipodeMoneda"],
                        Monto = (decimal)result["Monto"],
                        Simbolo = (string)result["Simbolo"]
                    });

                }
                dataHelper.CloseReader();
                return balanceComprobacion;
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return null;
            }
        }

        /// <summary>
        /// Método que permite obtener el estado de resultados de una empresa
        /// </summary>
        /// <param name="companyId"></param>
        /// <returns>Compañia que se desea obtener el estado de resultados</returns>
        public List<Reportes> GetEstadoResultados(int companyId)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_INCOME_STATEMENT);
                var parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_GET_ALL_ENTRIES_COMPANY_ID), companyId);
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                var balanceComprobacion = new List<Reportes>();
                while (result.Read())
                {
                    balanceComprobacion.Add(new Reportes
                    {
                        Codigo =
                            (string)
                            result[
                                appConfig.GetProperty(
                                    Constants.SP_OUTPUT_GET_ACCOUNTING_CATALOG_ACCOUNT_CODE)],
                        NombreExtranjero =
                            (string)
                            result[
                                appConfig.GetProperty(
                                    Constants.SP_OUTPUT_GET_ACCOUNTING_CATALOG_ACCOUNT_FOREIGN_NAME)],
                        NombreCuenta =
                            (string)
                            result[
                                appConfig.GetProperty(Constants.SP_PARAM_INSERT_ACCOUNT_NAME)
                                ],
                        CedulaJuridica =
                                 (string)
                            result[
                                appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANIES_COMPANY_TAX_IDENTIFICATION)
                                ],
                        NombreEmpresa =
                         (string)
                            result[
                                appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANIES_COMPANY_NAME)
                                ],
                        //Todo hacer esto bonito
                        Tipo = (string)result["Tipo"],
                        TipoMoneda = (string)result["TipodeMoneda"],
                        Monto =(decimal)result["Monto"],
                        Simbolo = (string)result["Simbolo"]
                    });
                }
                dataHelper.CloseReader();
                return balanceComprobacion;
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return null;
            }
        }

        /// <summary>
        /// Método que permite obtener el estado de resultados de una empresa
        /// </summary>
        /// <param name="companyId"></param>
        /// <returns>Compañia que se desea obtener el estado de resultados</returns>
        public List<Reportes> GetBalanceGeneral(int companyId)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_GENERAL_BALANCE);
                var parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_GET_ALL_ENTRIES_COMPANY_ID), companyId);
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                var balanceComprobacion = new List<Reportes>();
                while (result.Read())
                {
                    balanceComprobacion.Add(new Reportes
                    {
                        Codigo =
                            (string)
                            result[
                                appConfig.GetProperty(
                                    Constants.SP_OUTPUT_GET_ACCOUNTING_CATALOG_ACCOUNT_CODE)],
                        NombreExtranjero =
                            (string)
                            result[
                                appConfig.GetProperty(
                                    Constants.SP_OUTPUT_GET_ACCOUNTING_CATALOG_ACCOUNT_FOREIGN_NAME)],
                        NombreCuenta =
                            (string)
                            result[
                                appConfig.GetProperty(Constants.SP_PARAM_INSERT_ACCOUNT_NAME)
                                ],
                        CedulaJuridica =
                                 (string)
                            result[
                                appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANIES_COMPANY_TAX_IDENTIFICATION)
                                ],
                        NombreEmpresa =
                         (string)
                            result[
                                appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANIES_COMPANY_NAME)
                                ],
                        //Todo hacer esto bonito
                        Tipo = (string)result["Tipo"],
                        TipoMoneda = (string)result["TipodeMoneda"],
                        Monto = (decimal)result["Monto"],
                        Simbolo = (string)result["Simbolo"]
                    });
                }
                dataHelper.CloseReader();
                return balanceComprobacion;
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return null;
            }
        }

        /// <summary>
        /// Permite obtener las monedas de una compañia
        /// </summary>
        /// <param name="companyId">identificador de la compañia</param>
        /// <returns>Las Monedas de una compañia</returns>
        public List<Currency> GetCompanyCurrencies(int companyId)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_GET_COMPANY_CURRENCIES);
                var parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_GET_COMPANY_CURRENCIES_COMPANY_ID), companyId);
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                var ids = new List<Currency>();
                while (result.Read())
                {
                    var currencyId = Convert.ToInt32((byte)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANY_CURRENCIES_CURRENCY_ID)]);
                    var type = (string)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_COMPANY_CURRENCIES_CURRENCY_TYPE)];
                    var moneda = new Currency {Type = type, CurrencyId = currencyId};
                    ids.Add(moneda);
                }
                dataHelper.CloseReader();
                return ids;
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return null;
            }
        }

        /// <summary>
        /// Permite cambiar el estado de un mes contable a otro
        /// </summary>
        /// <param name="monthId">mes al que se le quiere cambiar el estado</param>
        /// <param name="monthStatus">estado que se convertira en el nuevo mes</param>
        /// <returns>Booleano indicando el estado de la operacion realizada</returns>
        public bool ChangeMonthStatus(int monthId, string monthStatus)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_CHANGE_MONTH_STATUS);
                var parameters = new SqlParameter[2];
                parameters[0] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_CHANGE_MONTH_STATUS_MONTH_ID), monthId);
                parameters[1] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_CHANGE_MONTH_STATUS_MONTH_STATUS), monthStatus);
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                return GetResult(dataHelper.ExecuteStoredProcedure(spName, parameters), dataHelper);
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return false;
            }
        }


        /// <summary>
        /// Permite obtener todas las ciudades de un estado
        /// </summary>
        /// <returns>Lista de ciudades de un estado</returns>
        public List<City> GetCities(int stateId)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_GET_CITIES);
                var parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_GET_CITIES_STATE_ID), stateId);
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                var cities = new List<City>();
                while (result.Read())
                {
                    var cityId = (int)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_CITIES_CITY_ID)];
                    var cityName = (string)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_CITIES_CITY_NAME)];
                    cities.Add(new City(cityId, stateId, cityName));
                }
                dataHelper.CloseReader();
                return cities;
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return null;
            }
        }

        /// <summary>
        /// Permite añadir telefonos a una compañía
        /// </summary>
        /// <param name="phoneType">Tipo de telefono</param>
        /// <param name="companyId">Id de la compañía</param>
        /// <param name="telefono">Telefono de la compañia</param>
        /// <returns>Booleano que indica si el telefono se añadio correctamente</returns>
        public bool AddPhonesToCompany(string phoneType, int companyId, string telefono)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_ADD_PHONES_TO_COMPANY);
                var parameters = new SqlParameter[3];
                parameters[0] =
                    new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_ADD_PHONES_TO_COMPANY_PHONE_TYPE),
                                     phoneType);
                parameters[1] =
                    new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_ADD_PHONES_TO_COMPANY_COMPANY_ID),
                                     companyId);
                parameters[2] =
                    new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_ADD_PHONES_TO_COMPANY_TELEFONO), telefono);
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                return GetResult(dataHelper.ExecuteStoredProcedure(spName, parameters), dataHelper);
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return false;
            }
        }

        /// <summary>
        /// Permite obtener los estados para un pais
        /// </summary>
        /// <param name="paisId">Compañia a insertar dentro de la aplicación</param>
        /// <returns>Retorna una lista de estados</returns>
        public List<State> GetStates(int paisId)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_GET_STATES);
                var parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_GET_STATES_COUNTRY_ID), paisId);
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                var states = new List<State>();
                while (result.Read())
                {
                    var stateId = (int)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_STATES_STATE_ID)];
                    var countryId = (int)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_STATES_COUNTRY_ID)];
                    var nombre = (string)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_STATES_NOMBRE)];
                    states.Add(new State(stateId, countryId, nombre));
                }
                dataHelper.CloseReader();
                return states;
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return null;
            }
        }


        /// <summary>
        /// Permite obtener los paises
        /// </summary>
        /// <returns>Lista de los paises</returns>
        public List<Country> GetCountries()
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_GET_COUNTRIES);
                var parameters = new SqlParameter[0];
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                var countries = new List<Country>();
                while (result.Read())
                {
                    var countryId = (int)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_COUNTRIES_COUNTRY_ID)];
                    var nombre = (string)result[appConfig.GetProperty(Constants.SP_OUTPUT_GET_COUNTRIES_NOMBRE)];
                    countries.Add(new Country(countryId, nombre));
                }
                dataHelper.CloseReader();
                return countries;
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return null;
            }
        }



        #region Protected / Private Methods

        /// <summary>
        /// Dice si el Stored Procedure fue efectuado correctamente o no
        /// </summary>
        /// <param name="result">ResultSet que indica si fue efectuado correctamente o no</param>
        /// <param name="helper">DataHelper con que la operación fue efectuada</param>
        /// <returns>Verdadero si el SP se ejecutó bien, de lo contrario falso</returns>
        private static bool GetResult(SqlDataReader result, SqlDataHelper helper)
        {
            result.Read();
            bool actionPerformed =
                Convert.ToInt32(
                    result[AppConfigReader.GetInstance().GetProperty(Constants.SP_OUTPUT_ACTION_PERFORMED_RESULT)]) == 0
                    ? false
                    : true;
            helper.CloseConnection();
            return actionPerformed;
        }

        #endregion

        #region Singleton

        /// <summary>
        /// Única instancia de esta clase
        /// </summary>
        private static CompanyDataManager _instance;

        private CompanyDataManager()
        {
        }

        /// <summary>
        /// Método para implementar el patrón de Singleton
        /// </summary>
        /// <returns>Instancia única de la clase</returns>
        public static CompanyDataManager GetInstance()
        {
            if (_instance == null)
                _instance = new CompanyDataManager();
            return _instance;
        }

        #endregion
    }
}
